package com.studentmanagement.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.studentmanagement.model.Course;
import com.studentmanagement.util.PageBean;

public class CourseDAO extends BaseDAO<Course> {
    
    // 添加课程
    public int addCourse(Course course) throws SQLException {
        String sql = "INSERT INTO courses (course_id, course_name, credit_hours, description, teacher) " +
                     "VALUES (course_seq.NEXTVAL, ?, ?, ?, ?)";
        
        return update(sql, course.getCourseName(), course.getCreditHours(), 
                     course.getDescription(), course.getTeacher());
    }
    
    // 更新课程信息
    public int updateCourse(Course course) throws SQLException {
        String sql = "UPDATE courses SET course_name = ?, credit_hours = ?, description = ?, teacher = ? " +
                     "WHERE course_id = ?";
        
        return update(sql, course.getCourseName(), course.getCreditHours(), 
                     course.getDescription(), course.getTeacher(), course.getCourseId());
    }
    
    // 删除课程
    public int deleteCourse(int courseId) throws SQLException {
        String sql = "DELETE FROM courses WHERE course_id = ?";
        return update(sql, courseId);
    }
    
    // 根据ID查询课程
    public Course getCourseById(int courseId) throws SQLException {
        String sql = "SELECT * FROM courses WHERE course_id = ?";
        
        return queryForObject(sql, new RowMapper<Course>() {
            @Override
            public Course mapRow(ResultSet rs) throws SQLException {
                Course course = new Course();
                course.setCourseId(rs.getInt("course_id"));
                course.setCourseName(rs.getString("course_name"));
                course.setCreditHours(rs.getInt("credit_hours"));
                course.setDescription(rs.getString("description"));
                course.setTeacher(rs.getString("teacher"));
                return course;
            }
        }, courseId);
    }
    
    // 分页查询课程列表
    public List<Course> getCoursesByPage(PageBean<Course> pageBean) throws SQLException {
        // 构建三层子查询SQL
        String sql = "SELECT * FROM (" +
                     "  SELECT a.*, ROWNUM rn FROM (" +
                     "    SELECT * FROM courses";
        
        // 添加排序条件
        if (pageBean.getSortField() != null && !pageBean.getSortField().isEmpty()) {
            sql += " ORDER BY " + pageBean.getSortField() + " " + pageBean.getSortOrder();
        } else {
            sql += " ORDER BY course_id ASC";
        }
        
        sql += "  ) a WHERE ROWNUM <= ?" +
               ") WHERE rn > ?";
        
        int end = pageBean.getPageNumber() * pageBean.getPageSize();
        int start = (pageBean.getPageNumber() - 1) * pageBean.getPageSize();
        
        return queryForList(sql, new RowMapper<Course>() {
            @Override
            public Course mapRow(ResultSet rs) throws SQLException {
                Course course = new Course();
                course.setCourseId(rs.getInt("course_id"));
                course.setCourseName(rs.getString("course_name"));
                course.setCreditHours(rs.getInt("credit_hours"));
                course.setDescription(rs.getString("description"));
                course.setTeacher(rs.getString("teacher"));
                return course;
            }
        }, end, start);
    }
    
    // 获取课程总数
    public int getCourseCount() throws SQLException {
        String sql = "SELECT COUNT(*) FROM courses";
        return getCount(sql);
    }
    
    // 根据教师查询课程
    public List<Course> getCoursesByTeacher(String teacher) throws SQLException {
        String sql = "SELECT * FROM courses WHERE teacher = ?";
        
        return queryForList(sql, new RowMapper<Course>() {
            @Override
            public Course mapRow(ResultSet rs) throws SQLException {
                Course course = new Course();
                course.setCourseId(rs.getInt("course_id"));
                course.setCourseName(rs.getString("course_name"));
                course.setCreditHours(rs.getInt("credit_hours"));
                course.setDescription(rs.getString("description"));
                course.setTeacher(rs.getString("teacher"));
                return course;
            }
        }, teacher);
    }
}

